/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Carrera;
import com.javabeans.Materia;
/**
 * @author Administrador
 */
public class AdminMateria implements MateriaManager{
    private Connection c;

    public AdminMateria(Connection c){
        this.c = c;
    }
    
    @Override
    public int getIdMateria(int idCarrera) throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDMATERIA)+1,1) AS ID FROM MATERIA WHERE IDCARRERA="+idCarrera;
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> getIdMateria() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    @Override
    public void saveMateria(Materia m)throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO MATERIA VALUES("+m.getIdCarrera();
            query += ", "+m.getIdMateria();
            query += ", '"+m.getNombre().toUpperCase();
            query += "', '"+m.getSigla().toUpperCase();
            query += "', "+m.isActivo()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCarrera -> saveMateria(Materia m) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void updateMateria(Materia m) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE MATERIA SET NOMBRE = '"+m.getNombre().toUpperCase();
            query+= "', SIGLA = '"+m.getSigla().toUpperCase();
            query+= "' WHERE IDCARRERA="+m.getIdCarrera()+" AND IDMATERIA="+m.getIdMateria();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCarrera -> updateCarrera(Carrera ca) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<Materia> getMateriasPorCarrera(int idCarrera) throws DataException{
        List<Materia> materias = new ArrayList<Materia>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT C.IDFACULTAD";
            query += ", C.NOMBRE, C.SIGLA";
            query += ", M.IDMATERIA, M.NOMBRE";
            query += ", M.SIGLA";
            query += " FROM MATERIA M";
            query += " INNER JOIN CARRERA C ON M.IDCARRERA=C.IDCARRERA AND C.IDCARRERA="+idCarrera;
            query += " WHERE M.ACTIVO=1";
            query += " ORDER BY M.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Carrera ca = new Carrera();
                ca.setIdCarrera(idCarrera);
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setIdFacultad(rs.getInt("C.IDFACULTAD"));
                
                Materia m = new Materia();
                m.setIdCarrera(idCarrera);
                m.setIdMateria(rs.getInt("M.IDMATERIA"));
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                m.setActivo(true);
                m.setCarrera(ca);
                materias.add(m);
            }
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> getMateriasPorCarrera(int idCarrera) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return materias;
    }
    
    @Override
    public List<Materia> getMateriasPorFacultadBasic(int idFacultad) throws DataException{
        List<Materia> materias = new ArrayList<Materia>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT C.IDCARRERA";
            query += ", M.IDMATERIA, M.NOMBRE";
            query += ", M.SIGLA";
            query += " FROM MATERIA M";
            query += " INNER JOIN (MATERIA_PENSUM MP INNER JOIN PENSUM P ON MP.IDPENSUM=P.IDPENSUM AND P.VIGENTE=1)";
            query += " ON M.IDCARRERA=MP.IDCARRERA AND M.IDMATERIA=MP.IDMATERIA AND MP.ACTIVO=1";
            query += " INNER JOIN CARRERA C ON M.IDCARRERA=C.IDCARRERA";
            query += " AND C.IDFACULTAD="+idFacultad;
            query += " ORDER BY C.NOMBRE, M.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Materia m = new Materia();
                m.setIdCarrera(rs.getInt("C.IDCARRERA"));
                m.setIdMateria(rs.getInt("M.IDMATERIA"));
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                materias.add(m);
            }
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> getMateriasPorFacultadBasic(int idFacultad) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return materias;
    }
    
    @Override
    public List<Materia> getMateriasInactivosPorCarrera(int idCarrera) throws DataException{
        List<Materia> materias = new ArrayList<Materia>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT C.IDFACULTAD";
            query += ", C.NOMBRE, C.SIGLA";
            query += ", M.IDMATERIA, M.NOMBRE";
            query += ", M.SIGLA";
            query += " FROM MATERIA M";
            query += " INNER JOIN CARRERA C ON M.IDCARRERA=C.IDCARRERA AND C.IDCARRERA="+idCarrera;
            query += " WHERE M.ACTIVO=0";
            query += " ORDER BY M.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Carrera ca = new Carrera();
                ca.setIdCarrera(idCarrera);
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setIdFacultad(rs.getInt("C.IDFACULTAD"));
                
                Materia m = new Materia();
                m.setIdCarrera(idCarrera);
                m.setIdMateria(rs.getInt("M.IDMATERIA"));
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                m.setActivo(false);
                m.setCarrera(ca);
                materias.add(m);
            }
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> getMateriasInactivosPorCarrera() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return materias;
    }
    
    // es un método especial, verificar muy bien antes de usar
    public List<Materia> getMateriasPropuesto(int idPensum,int idCarrera) throws DataException{
        List<Materia> materias = new ArrayList<Materia>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT M.IDCARRERA, M.IDMATERIA, M.NOMBRE, M.SIGLA";
            query += " FROM MATERIA M";
            query += " WHERE M.IDCARRERA="+idCarrera;
            query += " AND M.IDMATERIA NOT IN (SELECT MP.IDMATERIA FROM MATERIA_PENSUM MP INNER JOIN PENSUM P ON MP.IDPENSUM=P.IDPENSUM AND P.VIGENTE=1 WHERE MP.IDPENSUM="+idPensum+" AND MP.ACTIVO=1)";
            query += " ORDER BY M.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Materia m = new Materia();
                m.setIdCarrera(rs.getInt("M.IDCARRERA"));
                m.setIdMateria(rs.getInt("M.IDMATERIA"));
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                materias.add(m);
            }
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> getMateriasPropuesto(int idPensum,int idCarrera) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return materias;
    }
    
    @Override
    public List<Materia> getMateriasPorGrupo(int idGestion, int idGrupo) throws DataException{
        List<Materia> materias = new ArrayList<Materia>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT M.NOMBRE, M.SIGLA";
            query += ", C.NOMBRE, C.SIGLA";
            query += " FROM GRUPO_MATERIA GM";
            query += " INNER JOIN MATERIA M ON GM.IDCARRERA=M.IDCARRERA AND GM.IDMATERIA=M.IDMATERIA";
            query += " INNER JOIN CARRERA C ON GM.IDCARRERA=C.IDCARRERA";
            query += " WHERE GM.IDGESTION="+idGestion+" AND GM.IDGRUPO="+idGrupo;
            query += " ORDER BY M.NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Carrera ca = new Carrera();
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                
                Materia m = new Materia();
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                m.setCarrera(ca);
                materias.add(m);
            }
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> getMateriasPorGrupo(int idGestion, int idGrupo) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return materias;
    }
    
    @Override
    public Materia getMateria(int idCarrera, int idMateria) throws DataException{
        Materia m = new Materia();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT C.IDFACULTAD";
            query += ", C.NOMBRE, C.SIGLA";
            query += ", M.IDMATERIA, M.NOMBRE";
            query += ", M.SIGLA, M.ACTIVO";
            query += " FROM MATERIA M";
            query += " INNER JOIN CARRERA C ON M.IDCARRERA=C.IDCARRERA AND C.IDCARRERA="+idCarrera;
            query += " WHERE M.IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;           
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                Carrera ca = new Carrera();
                ca.setIdCarrera(idCarrera);
                ca.setNombre(rs.getString("C.NOMBRE"));
                ca.setSigla(rs.getString("C.SIGLA"));
                ca.setIdFacultad(rs.getInt("C.IDFACULTAD"));
                
                m.setIdCarrera(idCarrera);
                m.setIdMateria(rs.getInt("M.IDMATERIA"));
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                m.setActivo(rs.getBoolean("M.ACTIVO"));
                m.setCarrera(ca);
            }
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> getMateria(int idCarrera, int idMateria) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return m;
    }
    
    @Override
    public void deleteMateria(int idCarrera, int idMateria) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "DELETE FROM MATERIA WHERE IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> deleteMateria(int idCarrera) e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public boolean isUseMateriaForDelete(int idCarrera, int idMateria) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDMATERIA_PENSUM";
            query += " FROM MATERIA_PENSUM";
            query += " WHERE IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminMateria -> isUseMateriaForDelete(int idCarrera,int idMateria) sql: "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void darBaja(int idCarrera, int idMateria) throws DataException{
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "UPDATE MATERIA SET ACTIVO = 0 WHERE IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminMateria -> darBaja(int idCarrera, int idMateria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darAlta(int idCarrera, int idMateria) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE MATERIA SET ACTIVO = 1 WHERE IDCARRERA="+idCarrera+" AND IDMATERIA="+idMateria;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminMateria -> darAlta(int idCarrera, int idMateria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }

    
}
